package com.sixks.app.dao;

import com.sixks.app.domain.AttrName;
import com.sixks.app.domain.AttrValue;
import com.sixks.app.domain.Category;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * <p>Title: JdbcCategoryDao.java </p>
 * <p>Description: </p>
 * <p>Company: Snail Game </p>
 *
 * @author Xinying Ge
 * @date: 03/05/2014
 * @time: 18:51
 */
@Component
public class JdbcCategoryDao implements CategoryDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final String SQL_FIND_ALL =
            "select id, zh, en, isleaf from express_category where isleaf = 'true'";

    private static final String SQL_FIND_NAME_BY_ID =
            "select cateNameId,cateNameZh,cateNameEN from express_category_attrname where cateId = ?";

    private static final String SQL_FIND_VAL_BY_ID =
            "select valId, valZh,valEN from express_category_attrvalue where cateId=? and nameId = ?";


    @Override
    public List<Category> loadAll() {

        return jdbcTemplate.query(SQL_FIND_ALL, new RowMapper<Category>() {
            @Override
            public Category mapRow(ResultSet resultSet, int i) throws SQLException {
                Category category = new Category();
                category.setId(resultSet.getInt("id"));
                category.setZhName(resultSet.getString("zh"));
                category.setEnName(resultSet.getString("en"));
                category.setIsleaf("true".equals(resultSet.getString("isleaf")));
                return category;
            }
        });
    }

    @Override
    public List<AttrName> loadByCategoryId(int id) {
        return jdbcTemplate.query(SQL_FIND_NAME_BY_ID, new Object[]{id} ,new RowMapper<AttrName>() {
            @Override
            public AttrName mapRow(ResultSet resultSet, int i) throws SQLException {
                AttrName category = new AttrName();
                category.setId(""+resultSet.getInt("cateNameId"));
                category.setNamesEn(resultSet.getString("cateNameEN"));
                category.setNamesZh(resultSet.getString("cateNameZh"));
                return category;
            }
        });
    }

    @Override
    public List<AttrValue> loadByAttrNameId(int cateId, int nameId) {
        return jdbcTemplate.query(SQL_FIND_VAL_BY_ID, new Object[]{cateId, nameId} ,new RowMapper<AttrValue>() {
            @Override
            public AttrValue mapRow(ResultSet resultSet, int i) throws SQLException {
                AttrValue category = new AttrValue();
                category.setId(""+resultSet.getInt("valId"));
                category.setEn(resultSet.getString("valEN"));
                category.setZh(resultSet.getString("valZh"));
                return category;
            }
        });
    }
}
